﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using REPORT.DTO;
namespace REPORT.DAL
{
    public class DAL_HOADON
    {
        public int ThemHD(DTO_HOADON obj)
        {
            DbProvider db = new DbProvider();
            int result = 0;
            string strInsert = @"INSERT INTO hoadon (ma_khach_hang,ma_nguoi_dung, ngay_lap) VALUES(@makh,@mand,@ngaylap)";
            db.AddParameter(new SqlParameter("@makh", obj.MaKhachHang));
            db.AddParameter(new SqlParameter("@mand", obj.MaNguoiDung));
            db.AddParameter(new SqlParameter("@ngaylap", obj.NgayLap));
            try
            {
                result = db.ExcuteNoneQuery(strInsert);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return result;
        }
        public int SuaNgayLap(DTO_HOADON obj)
        {
            DbProvider db = new DbProvider();
            int result = 0;
            string strInsert = @"UPDATE hoadon SET ngay_lap=@ngaylap WHERE ma_hoa_don=@mahd";
            db.AddParameter(new SqlParameter("@ngaylap", obj.NgayLap));
            db.AddParameter(new SqlParameter("@mahd", obj.MaHoaDon));
            try
            {
            result = db.ExcuteNoneQuery(strInsert);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return result;
        }
        public DataTable IdMax()
        {
            DataTable dt = new DataTable();
            DbProvider db = new DbProvider();
            string sql = @"select * from hoadon where ma_hoa_don=(SELECT IDENT_CURRENT( 'hoadon'))";
            try
            {
                dt = db.ExecuteQuery(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return dt;
        }
        public DataTable LoadMaHD()
        {
            DataTable dt = new DataTable();
            DbProvider db = new DbProvider();
            string sql = @"select ma_hoa_don from hoadon";
            try
            {
                dt = db.ExecuteQuery(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return dt;
        }
        public DataTable ThongTinTheoHD(int mahd)
        {
            DataTable dt = new DataTable();
            DbProvider db = new DbProvider();
            string sql = @"select ten_khach_hang from khachhang where ma_khach_hang=N'" + mahd + "'";
            try
            {
                dt = db.ExecuteQuery(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return dt;
        }
        public DataTable ThongTinTheoHDDuocChon(int mahd)
        {
            DataTable dt = new DataTable();
            DbProvider db = new DbProvider();
            string sql = @"select ho_ten,ten_khach_hang, dia_chi, so_dien_thoai from (HoaDon inner join KhachHang on HoaDon.ma_khach_hang = KhachHang.ma_khach_hang)inner join NguoiDung
	                        on NguoiDung.ma_nguoi_dung = HoaDon.ma_nguoi_dung
	                        where HoaDon.ma_hoa_don = @mahd";
            db.AddParameter(new SqlParameter("@mahd", mahd));
            try
            {
                dt = db.ExecuteQuery(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return dt;
        }
        public DataSet LoadTheoMaHD(int mahd)
        {
            DataSet ds = new DataSet();
            DbProvider db = new DbProvider();
            string sql = @"select ma_hoa_don, chitiethoadon.ma_san_pham, ten_san_pham, chitiethoadon.so_luong, chitiethoadon.don_gia, 
                            ChiTietHoaDon.don_vi_tinh,
                            chitiethoadon.so_luong*chitiethoadon.don_gia as thanh_tien  
                            from chitiethoadon left join SanPham on 
                            ChiTietHoaDon.ma_san_pham = SanPham.ma_san_pham where ma_hoa_don=@mahd";
            db.AddParameter(new SqlParameter("@mahd", mahd));
            try
            {
                ds = db.DsExecuteQuery(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return ds;
        }
      }
}
